const { v4: uuidv4 } = require('uuid')
const safe = (str) =>
  str === undefined || str === null ? '' : String(str).replace(/'/g, "''")
// 格式化日期为 'YYYY-MM-DD HH:mm:ss'
function formatDateTime (date) {
  let d;
  if (!date) {
    d = new Date();
  } else {
    d = new Date(date);
    if (isNaN(d.getTime())) {
      // 非法时间，兜底为当前时间或 '1970-01-01 00:00:00'
      d = new Date();
    }
  }
  const pad = n => n < 10 ? '0' + n : n;
  return `${d.getFullYear()}-${pad(d.getMonth() + 1)}-${pad(d.getDate())} ${pad(d.getHours())}:${pad(d.getMinutes())}:${pad(d.getSeconds())}`;
}
exports.createWechatBillingInsertQuery = (data) => {
  const {
    id,
    trade_time,
    official_id,
    busi_no,
    sub_busi_no,
    equ_num,
    wx_trade_num,
    out_order_code,
    user_openid,
    trade_type,
    trade_stat,
    pay_bank,
    coin_type,
    order_should_fee,
    coupon_fee,
    wx_refund_num,
    busi_refund_num,
    refund_fee,
    charge_refund_fee,
    refund_type,
    refund_stat,
    goods_name,
    busi_data,
    service_fee,
    fee_ratio,
    order_fee,
    apply_refund_fee,
    fee_ratio_memo,
    create_time,
    platform_name_id,
    org_id,
    order_code_id,
    order_busi_type_dict_id,
    year,
    month,
    equ_category_dict_id,
    is_deposit_to_rent,
    is_no_deposit,
    rent,
    del_flg = 0,
  } = data
  // create_time 默认当前时间
  const query = `
    INSERT INTO wechat_billing (
      id, trade_time, official_id, busi_no, sub_busi_no, equ_num, wx_trade_num, out_order_code, user_openid,
      trade_type, trade_stat, pay_bank, coin_type, order_should_fee, coupon_fee, wx_refund_num, busi_refund_num,
      refund_fee, charge_refund_fee, refund_type, refund_stat, goods_name, busi_data, service_fee, fee_ratio,
      order_fee, apply_refund_fee, fee_ratio_memo, create_time, platform_name_id, org_id, order_code_id,
      order_busi_type_dict_id, year, month, equ_category_dict_id, is_deposit_to_rent, is_no_deposit, rent, del_flg
    ) VALUES (
      '${uuidv4()}',
      '${safe(formatDateTime(trade_time))}',
      '${safe(official_id)}',
      '${safe(busi_no)}',
      '${safe(sub_busi_no)}',
      '${safe(equ_num)}',
      '${safe(wx_trade_num)}',
      '${safe(out_order_code)}',
      '${safe(user_openid)}',
      '${safe(trade_type)}',
      '${safe(trade_stat)}',
      '${safe(pay_bank)}',
      '${safe(coin_type)}',
       ${isFinite(Number(order_should_fee)) ? Number(order_should_fee) : 0},
       ${isFinite(Number(coupon_fee)) ? Number(coupon_fee) : 0},
      '${safe(wx_refund_num)}',
      '${safe(busi_refund_num)}',
      ${isFinite(Number(refund_fee)) ? Number(refund_fee) : 0},
      ${isFinite(Number(charge_refund_fee)) ? Number(charge_refund_fee) : 0},
      '${safe(refund_type)}',
      '${safe(refund_stat)}',
      '${safe(goods_name)}',
      '${safe(busi_data)}',
      ${isFinite(Number(service_fee)) ? Number(service_fee) : 0},
      ${isFinite(Number(fee_ratio)) ? Number(fee_ratio) : 0},
      ${isFinite(Number(order_fee)) ? Number(order_fee) : 0},
     ${isFinite(Number(apply_refund_fee)) ? Number(apply_refund_fee) : 0},
      '${safe(fee_ratio_memo)}',
      '${safe(formatDateTime())}',
      ${platform_name_id || 0},
      ${org_id || 0},
      ${order_code_id || 0},
      ${order_busi_type_dict_id || 0},
      ${year || 0},
      ${month || 0},
      ${equ_category_dict_id || 0},
      ${is_deposit_to_rent || 0},
      ${is_no_deposit || 0},
      ${isFinite(Number(rent)) ? Number(rent) : 0},
      ${del_flg || 0}
    )
  `
  return query
}

//查询..数据
exports.searchEquipmentNumberQuery = (data) => {
  const { status, tags } = data
  let whereArr = []
  if (status) {
    whereArr.push(`status = '${safe(status)}'`)
  }
  if (tags?.length) {
    whereArr.push(`has(tags, '${safe(tags[0])}')`) // 只支持单标签过滤
  }
  const where = whereArr.length ? whereArr.join(' AND ') : '1=1'
  const query = `
      SELECT
        id, title, content, author_id, status, tags, created_at
      FROM article
      WHERE ${where}
      ORDER BY created_at DESC
    `
  return query
}
